还是之前的项目的数据库优化过程,这次换成了分析AWR报告中的TOP SQL

项目中有一个很长的SQL,篇幅原因就不给大家写出来了,一共4个UNION组合。。。很长。

一共这个sql查出来了20多条数据,但是用了2.1秒左右,考虑到该sql在业务中的实际应用情况,每2分钟都要刷新一次,这样的话对DB性能消耗也是个影响,于是决定优化这个SQL一下。

获取该sql的执行计划

 Plan Hash Value  : 1613327291 

---------------------------------------------------------------------------------------------------------------------------
| Id    | Operation                                   | Name                      | Rows   | Bytes     | Cost  | Time     |
---------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                            |                           |     40 |     45240 | 66239 | 00:13:15 |
|   * 1 |   VIEW                                      |                           |     40 |     45240 | 66239 | 00:13:15 |
|   * 2 |    COUNT STOPKEY                            |                           |        |           |       |          |
|     3 |     VIEW                                    |                           |   5586 |   6245148 | 66239 | 00:13:15 |
|   * 4 |      SORT UNIQUE STOPKEY                    |                           |   5586 |   3376642 | 65901 | 00:13:11 |
|     5 |       UNION-ALL                             |                           |        |           |       |          |
|     6 |        NESTED LOOPS OUTER                   |                           |   4919 |   1485538 | 43893 | 00:08:47 |
|   * 7 |         HASH JOIN                           |                           |   4919 |   1323211 | 29271 | 00:05:52 |
|   * 8 |          TABLE ACCESS BY INDEX ROWID        | RAMS_ALARM_DEAL           |  23800 |   2332400 |  8512 | 00:01:43 |
|     9 |           BITMAP CONVERSION TO ROWIDS       |                           |        |           |       |          |
|    10 |            BITMAP AND                       |                           |        |           |       |          |
|    11 |             BITMAP OR                       |                           |        |           |       |          |
|    12 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 13 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_DEALSTATE |        |           |   551 | 00:00:07 |
|    14 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 15 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_DEALSTATE |        |           |   122 | 00:00:02 |
|    16 |             BITMAP OR                       |                           |        |           |       |          |
|    17 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 18 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    19 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 20 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    21 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 22 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    23 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 24 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    25 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 26 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |   969 | 00:00:12 |
|    27 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 28 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    29 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 30 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    31 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 32 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    33 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 34 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    35 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 36 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    37 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 38 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    39 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 40 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    41 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 42 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    43 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 44 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |   163 | 00:00:02 |
|    45 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 46 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    47 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 48 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    49 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 50 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    51 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 52 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    53 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 54 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    55 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 56 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    57 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
|  * 58 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    59 |          TABLE ACCESS FULL                  | RAMS_ALARM_WORKSHEET      | 681549 | 116544879 | 14736 | 00:02:57 |
|    60 |         TABLE ACCESS BY INDEX ROWID         | RAMS_ALARMMAIN            |      1 |        33 |     3 | 00:00:01 |
|  * 61 |          INDEX RANGE SCAN                   | RAMS_ALARMMAIN_SHEETNO    |      1 |           |     2 | 00:00:01 |
|    62 |        NESTED LOOPS OUTER                   |                           |      1 |       344 | 15062 | 00:03:01 |
|    63 |         NESTED LOOPS                        |                           |      1 |       311 | 15059 | 00:03:01 |
|  * 64 |          HASH JOIN                          |                           |    273 |     38220 | 14240 | 00:02:51 |
|  * 65 |           TABLE ACCESS BY INDEX ROWID       | RAMS_ALARM_DEAL           |   6819 |    722814 |  4937 | 00:01:00 |
|    66 |            BITMAP CONVERSION TO ROWIDS      |                           |        |           |       |          |
|    67 |             BITMAP AND                      |                           |        |           |       |          |
|    68 |              BITMAP OR                      |                           |        |           |       |          |
|    69 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 70 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_DEALSTATE |        |           |   551 | 00:00:07 |
|    71 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 72 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_DEALSTATE |        |           |   122 | 00:00:02 |
|    73 |              BITMAP OR                      |                           |        |           |       |          |
|    74 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 75 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    76 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 77 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    78 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 79 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    80 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 81 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |   969 | 00:00:12 |
|    82 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 83 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    84 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 85 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    86 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 87 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    88 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 89 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    90 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 91 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    92 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 93 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    94 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 95 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    96 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 97 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|    98 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
|  * 99 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|   100 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 101 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |   163 | 00:00:02 |
|   102 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 103 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|   104 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 105 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|   106 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 107 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|   108 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 109 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|   110 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 111 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|   112 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 113 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|   114 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 115 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_B         |        |           |     7 | 00:00:01 |
|   116 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
| * 117 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_DEALTYPE  |        |           |  1389 | 00:00:17 |
|   118 |           VIEW                              | VW_SQ_1                   |  73352 |   2493968 |  9302 | 00:01:52 |
|   119 |            HASH GROUP BY                    |                           |  73352 |   3227488 |  9302 | 00:01:52 |
| * 120 |             INDEX FAST FULL SCAN            | RAMS_ALARM_DEAL_SHEETNO   |  76580 |   3369520 |  8468 | 00:01:42 |
|   121 |          TABLE ACCESS BY INDEX ROWID        | RAMS_ALARM_WORKSHEET      |      1 |       171 |     3 | 00:00:01 |
| * 122 |           INDEX RANGE SCAN                  | RAMS_WORKSHEET_WS_NUM     |      1 |           |     2 | 00:00:01 |
|   123 |         TABLE ACCESS BY INDEX ROWID         | RAMS_ALARMMAIN            |      1 |        33 |     3 | 00:00:01 |
| * 124 |          INDEX RANGE SCAN                   | RAMS_ALARMMAIN_SHEETNO    |      1 |           |     2 | 00:00:01 |
|   125 |        NESTED LOOPS OUTER                   |                           |    665 |    202160 |  6620 | 00:01:20 |
|   126 |         NESTED LOOPS                        |                           |    661 |    179131 |  4656 | 00:00:56 |
| * 127 |          TABLE ACCESS BY INDEX ROWID        | RAMS_ALARM_DEAL           |    661 |     66100 |  2672 | 00:00:33 |
|   128 |           BITMAP CONVERSION TO ROWIDS       |                           |        |           |       |          |
|   129 |            BITMAP AND                       |                           |        |           |       |          |
|   130 |             BITMAP CONVERSION FROM ROWIDS   |                           |        |           |       |          |
| * 131 |              INDEX RANGE SCAN               | RAMS_ALARM_DEAL_DEALTYPE  |  61440 |           |   145 | 00:00:02 |
|   132 |             BITMAP OR                       |                           |        |           |       |          |
|   133 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
| * 134 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_DEALSTATE |  61440 |           |   551 | 00:00:07 |
|   135 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
| * 136 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_DEALSTATE |  61440 |           |   122 | 00:00:02 |
|   137 |          TABLE ACCESS BY INDEX ROWID        | RAMS_ALARM_WORKSHEET      |      1 |       171 |     3 | 00:00:01 |
| * 138 |           INDEX RANGE SCAN                  | RAMS_WORKSHEET_WS_NUM     |      1 |           |     2 | 00:00:01 |
|   139 |         TABLE ACCESS BY INDEX ROWID         | RAMS_ALARMMAIN            |      1 |        33 |     3 | 00:00:01 |
| * 140 |          INDEX RANGE SCAN                   | RAMS_ALARMMAIN_SHEETNO    |      1 |           |     2 | 00:00:01 |
| * 141 |        FILTER                               |                           |        |           |       |          |
|   142 |         NESTED LOOPS OUTER                  |                           |      1 |       279 |    35 | 00:00:01 |
|   143 |          NESTED LOOPS                       |                           |      1 |       246 |    32 | 00:00:01 |
| * 144 |           TABLE ACCESS BY INDEX ROWID       | RAMS_ALARM_DEAL           |      1 |        75 |    29 | 00:00:01 |
|   145 |            BITMAP CONVERSION TO ROWIDS      |                           |        |           |       |          |
|   146 |             BITMAP AND                      |                           |        |           |       |          |
|   147 |              BITMAP CONVERSION FROM ROWIDS  |                           |        |           |       |          |
| * 148 |               INDEX RANGE SCAN              | RAMS_ALARM_DEAL_DEALROLE  |    449 |           |     4 | 00:00:01 |
|   149 |              BITMAP OR                      |                           |        |           |       |          |
|   150 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 151 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_DEALTYPE  |    449 |           |    10 | 00:00:01 |
|   152 |               BITMAP CONVERSION FROM ROWIDS |                           |        |           |       |          |
| * 153 |                INDEX RANGE SCAN             | RAMS_ALARM_DEAL_DEALTYPE  |    449 |           |    15 | 00:00:01 |
| * 154 |           TABLE ACCESS BY INDEX ROWID       | RAMS_ALARM_WORKSHEET      |      1 |       171 |     3 | 00:00:01 |
| * 155 |            INDEX RANGE SCAN                 | RAMS_WORKSHEET_WS_NUM     |      1 |           |     2 | 00:00:01 |
|   156 |          TABLE ACCESS BY INDEX ROWID        | RAMS_ALARMMAIN            |      1 |        33 |     3 | 00:00:01 |
| * 157 |           INDEX RANGE SCAN                  | RAMS_ALARMMAIN_SHEETNO    |      1 |           |     2 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------

看这个执行计划,发现计划中有很多BITMAP CONVERSION TO ROWIDS 字样,看起来貌似用了位图索引。

位图索引说明:

引用资料:http://www.cnblogs.com/LBSer/...

从这篇资料中可以分析出来,位图索引使用的场景为:
位图索引适合静态数据,而不适合索引频繁更新的列

但是我们的表没有建立bitmap类型的索引,询问过开发,都是建立的Normal索引,这是为什么呢?

通过询问公司DBA同事,推荐了一篇文档给我:

引用资料:http://raugher.blog.51cto.com...

**看到这里:
出现这样的情况,是因为表中存在不适当的索引,这些索引列的唯一度不高,oracle就有可能选择两个这样的索引转为bitmap来执行**

之后询问开发,发现有一张表中建立了很多不能通过该字段判断唯一性的索引列,这样导致oracle自行判断,就在执行计划中触发了bitmap。

知道原因后,将表中不能判断唯一性的字段索引去掉,重新执行SQL,观察执行计划:

 Plan Hash Value  : 1709251740 

----------------------------------------------------------------------------------------------------------------
| Id   | Operation                             | Name                   | Rows   | Bytes    | Cost  | Time     |
----------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                      |                        |     40 |    45240 | 71112 | 00:14:14 |
|  * 1 |   VIEW                                |                        |     40 |    45240 | 71112 | 00:14:14 |
|  * 2 |    COUNT STOPKEY                      |                        |        |          |       |          |
|    3 |     VIEW                              |                        |  39042 | 43648956 | 71112 | 00:14:14 |
|  * 4 |      SORT UNIQUE STOPKEY              |                        |  39042 | 22727766 | 68852 | 00:13:47 |
|    5 |       UNION-ALL                       |                        |        |          |       |          |
|  * 6 |        HASH JOIN OUTER                |                        |  37719 | 10976229 | 37682 | 00:07:33 |
|  * 7 |         HASH JOIN                     |                        |  35734 |  9219372 | 17105 | 00:03:26 |
|  * 8 |          TABLE ACCESS FULL            | RAMS_ALARM_DEAL        |  15417 |  1464615 |  7353 | 00:01:29 |
|    9 |          TABLE ACCESS FULL            | RAMS_ALARM_WORKSHEET   | 457958 | 74647154 |  9751 | 00:01:58 |
|   10 |         TABLE ACCESS FULL             | RAMS_ALARMMAIN         | 458155 | 15119115 | 19143 | 00:03:50 |
|   11 |        NESTED LOOPS OUTER             |                        |      1 |      332 | 15431 | 00:03:06 |
|   12 |         NESTED LOOPS                  |                        |      1 |      299 | 15427 | 00:03:06 |
| * 13 |          HASH JOIN                    |                        |    175 |    23800 | 15248 | 00:03:03 |
| * 14 |           TABLE ACCESS FULL           | RAMS_ALARM_DEAL        |   4396 |   452788 |  7347 | 00:01:29 |
|   15 |           VIEW                        | VW_SQ_1                |  49438 |  1631454 |  7900 | 00:01:35 |
|   16 |            HASH GROUP BY              |                        |  49438 |  2125834 |  7900 | 00:01:35 |
| * 17 |             TABLE ACCESS FULL         | RAMS_ALARM_DEAL        |  51696 |  2222928 |  7346 | 00:01:29 |
| * 18 |          TABLE ACCESS BY INDEX ROWID  | RAMS_ALARM_WORKSHEET   |      1 |      163 |     2 | 00:00:01 |
| * 19 |           INDEX RANGE SCAN            | RAMS_WORKSHEET_WS_NUM  |      1 |          |     1 | 00:00:01 |
|   20 |         TABLE ACCESS BY INDEX ROWID   | RAMS_ALARMMAIN         |      1 |       33 |     4 | 00:00:01 |
| * 21 |          INDEX RANGE SCAN             | RAMS_ALARMMAIN_SHEETNO |      1 |          |     2 | 00:00:01 |
|   22 |        NESTED LOOPS OUTER             |                        |   1321 |   387053 | 13375 | 00:02:41 |
|   23 |         NESTED LOOPS                  |                        |   1251 |   325260 |  8370 | 00:01:41 |
| * 24 |          TABLE ACCESS FULL            | RAMS_ALARM_DEAL        |    507 |    49179 |  7347 | 00:01:29 |
| * 25 |          TABLE ACCESS BY INDEX ROWID  | RAMS_ALARM_WORKSHEET   |      2 |      326 |     8 | 00:00:01 |
| * 26 |           INDEX RANGE SCAN            | RAMS_WORKSHEET_WS_NUM  |      7 |          |     1 | 00:00:01 |
|   27 |         TABLE ACCESS BY INDEX ROWID   | RAMS_ALARMMAIN         |      1 |       33 |     4 | 00:00:01 |
| * 28 |          INDEX RANGE SCAN             | RAMS_ALARMMAIN_SHEETNO |      1 |          |     2 | 00:00:01 |
| * 29 |        FILTER                         |                        |        |          |       |          |
|   30 |         NESTED LOOPS OUTER            |                        |      1 |      269 |  7357 | 00:01:29 |
|   31 |          NESTED LOOPS                 |                        |      1 |      236 |  7353 | 00:01:29 |
| * 32 |           TABLE ACCESS FULL           | RAMS_ALARM_DEAL        |      1 |       73 |  7345 | 00:01:29 |
| * 33 |           TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_WORKSHEET   |      1 |      163 |     8 | 00:00:01 |
| * 34 |            INDEX RANGE SCAN           | RAMS_WORKSHEET_WS_NUM  |      7 |          |     1 | 00:00:01 |
|   35 |          TABLE ACCESS BY INDEX ROWID  | RAMS_ALARMMAIN         |      1 |       33 |     4 | 00:00:01 |
| * 36 |           INDEX RANGE SCAN            | RAMS_ALARMMAIN_SHEETNO |      1 |          |     2 | 00:00:01 |
----------------------------------------------------------------------------------------------------------------

可以看到,这次的执行计划中就没有BITMAP CONVERSION TO ROWIDS了,在测试系统上进行测试,sql的执行时间为1.2秒,比之前快了不少。

但是将这个改动搬移到正式库上的时候,一会儿就有用户反映项目卡的不能用。。。

之后对比了下正式和测试库的数据量,发现正式库比测试库这几张表的数量级大了一倍多,并且都已经到了百万级以上。

观察大表是否有分区情况,答案是没有。。。。。so sad
看来SQL慢的原因不只是因为索引建立不当,跟库表设计还有关系。
这样的话后续优化方向基本明确了,交给开发部门吧。后续有进展的话继续更新此文。


少放香菜
56 声望3 粉丝